package org.groovyflow.db;

import groovy.lang.Closure;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.*;

import org.groovyflow.util.IgnoreCaseMap;


/**
 * A work in progress.
 * TODO  Put this in GroovyFlowContext.  Have insertActiveRecord that first uses search
 * to find the sequence.  In prod mode we'll probably findTableMetaData
 * on startup, rather than doing it when each object is instantiated,
 * in which case startup will need to create and clear a GroovyFlowContext in startup mode.
 *
 * We'll need to clear the tableCache when a file is modified. Could be that activeRecord
 * objects need to be in a separate source root from the regular conditionally compiled
 * Groovy classes.
 * @author Chuck Tassoni
 */
public class SqlDaoImpl implements SqlDao{
	private Map<String, Map> stmtCache = new HashMap<String, Map>();
	//In the table cache these names don't include the id field or any fields
	//that the activeRecord declared to be excluded.
	private Map<String, Map> tableCache = new HashMap<String, Map>();

	private boolean handlingTimeDates;


	/* (non-Javadoc)
	 * @see org.groovyflow.db.SqlDaoI#updateDelOrInsert(java.lang.String, java.lang.Object)
	 */
	public int updateDelOrInsert(String sql, Object obj){
		List params = new ArrayList();
		params.add(obj);
		return updateDelOrInsert(sql, params);
	}

	/* (non-Javadoc)
	 * @see org.groovyflow.db.SqlDaoI#updateDelOrInsert(java.lang.String, java.util.List)
	 */
	public int updateDelOrInsert(final String sql, final List params){
		Closure clos =  new Closure(null) {
	        public Object doCall(Object[] args) {
	    		System.out.println("sql = " + sql  + " with params " + params);
	    	    Connection conn = null;
	    	    PreparedStatement stmt = null;
	    	    try{
	    	    	conn = getConnection();
	    	    	stmt = conn.prepareStatement(sql);
	    	    	setParameters(stmt, params);
	    	    	return stmt.executeUpdate();
	    	    }catch(SQLException ex){
	    	    	ex.printStackTrace();
	    	    	throw new RuntimeException(ex);
	    	    }finally{
	    	    	close(stmt);
	    	    }
	        }
	    };

	   return ( (Integer) DbHelp.inTrx(clos)).intValue();
	}


	/* (non-Javadoc)
	 * @see org.groovyflow.db.SqlDaoI#doSearch(java.lang.String)
	 */
	public List<Map> doSearch(String sql){
		return doSearch(sql, new ArrayList());
	}

	/* (non-Javadoc)
	 * @see org.groovyflow.db.SqlDaoI#doSearch(java.lang.String, java.lang.Object)
	 */
	public List<Map> doSearch(String sql, Object param){
		List params = new ArrayList();
		params.add(param);
		return doSearch(sql, params, null);
	}

	/* (non-Javadoc)
	 * @see org.groovyflow.db.SqlDaoI#doSearch(java.lang.String, java.util.List)
	 */
	public List<Map> doSearch(String sql, List params){
		return doSearch(sql, params, null);
	}
	/* (non-Javadoc)
	 * @see org.groovyflow.db.SqlDaoI#doSearch(java.lang.String, java.util.List, org.groovyflow.db.ResultSetCallback)
	 */
	public List<Map> doSearch(final String sql,final List params, final ResultSetCallback callback){
		Closure clos =  new Closure(null) {
	        public Object doCall(Object[] args) {
	    		System.out.println("sql = " + sql  + " with params " + params);
	    	    Connection conn = null;
	    	    PreparedStatement stmt = null;
	    	    ResultSet rs = null;
	    	    try{
	    	    	conn = getConnection();
	    	    	stmt = conn.prepareStatement(sql);
	    	    	setParameters(stmt, params);
	    	    	rs = stmt.executeQuery();
	    	    	Map metaData = createOrFindMetaData(sql, rs, stmtCache);
	    	    	return load(rs, metaData, callback);
	    	    }catch(SQLException ex){
	    	    	ex.printStackTrace();
	    	    	throw new RuntimeException(ex);
	    	    }finally{
	    	    	close(rs, stmt);
	    	    }
	        }
	    };
		return ( List<Map>) DbHelp.inTrx(clos);
	}

	//TODO NOW  Change ActiveRecord paramter to a map that holds ActiveRecord info.
	//This will let me develop with an ActiveRecord that's in the conditional-compile
	//directory.
	/* (non-Javadoc)
	 * @see org.groovyflow.db.SqlDaoI#findTableMetaData(org.groovyflow.db.ActiveRecord)
	 */
	public void findTableMetaData(ActiveRecord rec){
		System.out.println("!!!!In findTableMetaData tableCache is" + this.tableCache);
		//TODO  This could make for bad performance, and sql statements
		//that get just one row or database dependant (unless I can just set a max amount
		//on the prepared statement.  So this might need to be a database specific paramater
		//Need to figure out.

		if(this.tableCache.get(rec.gTableName()) != null)
			return;

		System.out.println("!!!!Now finding metaData in findTableMetaData for table " + rec.gTableName());


		String sql = "select * from " + rec.gTableName();
	    Connection conn = null;
	    PreparedStatement stmt = null;
	    ResultSet rs = null;
	    try{
	    	conn = this.getConnection();
	    	stmt = conn.prepareStatement(sql);
	    	stmt.setMaxRows(1);
	    	rs = stmt.executeQuery();
	    	Map metaData = createOrFindMetaData(sql, rs, null);
	    	//TODO  THIS SHOULD BE A SET FOR PERFORMANCE!!  We end up calling contains()
	    	//on it very frequently.
	    	List columnNamesNotIncludingId = ((List) metaData.get(COLUMN_NAMES));
	    	excludeFields(columnNamesNotIncludingId, rec.gFieldsToExclude());

	    	columnNamesNotIncludingId.remove(rec.gIdName());
	    	ActiveRecordHelp help = new ActiveRecordHelp();
	    	metaData.put(UPDATE_STATMENT, help.makeUpdateStatement(columnNamesNotIncludingId, rec));
	    	metaData.put(INSERT_STATMENT, help.makeInsertStatement(columnNamesNotIncludingId, rec));
	    	this.tableCache.put(rec.gTableName(), metaData);

	    }catch(SQLException ex){
	    	ex.printStackTrace();
	    	throw new RuntimeException(ex);
	    }finally{
	    	close(rs, stmt);
	    }
	}

	private void excludeFields(List columnNames, Set fieldsToExclude){
		if(fieldsToExclude.isEmpty())
			return;
		for(ListIterator iter = columnNames.listIterator(); iter.hasNext(); ){
			if(fieldsToExclude.contains(iter.next()))
				iter.remove();
		}
	}

	/**
	 * It's possible that in development mode one could instantiate an
	 * ActiveRecord, modify a class in the conditional compilation directory,
	 * and then try to save that ActiveRecord.  But modifying the class
	 * will have caused our tableCache to be cleared, and so the save would fail.
	 * Calling this method when approprate can prevent this problem.
	 */
	private Map getMetaData(ActiveRecord rec){
		Map metaData = (Map) getTableCache().get(rec.gTableName());
		if(metaData == null){
			findTableMetaData(rec);
			metaData = (Map) getTableCache().get(rec.gTableName());
		}
		return metaData;
	}

	private List<Map> load(ResultSet rs, Map metaData, ResultSetCallback callback) throws SQLException{
		List<Map> rows = new ArrayList<Map>();
		int count = (Integer) metaData.get(COLUMN_COUNT);
		List names = (List) metaData.get(COLUMN_NAMES);
		while(rs.next()){
			if(callback != null)
				callback.receive(rs);
			Map row = new IgnoreCaseMap();
			for(int i = 1; i <= count; i++){
				Object obj = rs.getObject(i);
				//For oracle bug.
				if( (obj instanceof java.sql.Date || obj instanceof java.sql.Timestamp) && handlingTimeDates){
					obj = rs.getTimestamp(i);
					java.sql.Timestamp timestamp = (java.sql.Timestamp) obj;
					java.util.Date date = new java.util.Date(timestamp.getTime());
					obj = date;
				}
				row.put(names.get(i - 1),
					obj
				);
			}
			rows.add(row);
		}
		return rows;
	}

	/**
	 * The order of values is the same as the order of values in columnNamesNotIncludingId,
	 * after which comes the id, and then the version, if we have a version.
	 * If zero rows were updated and we have a version, we throw a StaleRecordException.
	 *
	 */
    //TODO Doesn't handle values added with a trigger yet!! Maybe we shouldn't support
	//that anyway.
	public void doActiveRecordUpdate(Map values, ActiveRecord rec){
		Map metaData = getMetaData(rec);
		List columnNamesNotIncludingId = (List) metaData.get(SqlDao.COLUMN_NAMES);
		List params = new LinkedList();

		String versionName = rec.gVersionFieldName();
		Integer oldVersion = (versionName == null) ? null : (Integer) values.get(versionName);

		for(int i = 0, m = columnNamesNotIncludingId.size(); i < m; i++){
			String columName = (String) columnNamesNotIncludingId.get(i);
			if(columName.equals(versionName)){
				Integer newVersion = oldVersion;
				newVersion++;
				params.add(newVersion);
			}
			params.add(values.get(columName));
		}

		params.add(values.get(rec.gIdName()));
		if(versionName != null)
			params.add(oldVersion);

		int modified = updateDelOrInsert((String) metaData.get(SqlDao.UPDATE_STATMENT),
				params);

		if(modified > 1)
			throw new RuntimeException(modified + " rows were updated for what we thought was" +
					" a single record.  Perhaps you did not configure the correct name of the id field.");
		if(modified != 1) {
			if(versionName != null)
				throw new StaleRecordException(versionName + " = " + oldVersion + " is stale");
			else
				throw new RuntimeException("No rows were updated on record update");
		}

	}

	/**
	 * The primary key appears first in our params list, all other fields are in order of the
	 * metaData's columnNamesNotIncludingId, and we take care to set
	 * the value of the version field to 1 if we have a version field.
	 * We get the primary key from a hit against a sequence table.
	 * The primary key is inserting into our values after the database insertion
	 * is complete.
	 */
	//TODO Doesn't handle values added with a trigger yet!!  Maybe we shouldn't support
	//that anyway.
	public void doActiveRecordInsert(Map values, ActiveRecord rec){
		//TODO  This is a database dependent item.
		//By the way, is there anyway I can get this without making an extra trip to
		//the database?
		String seqSql = "select " + rec.gPKSequenceName() + ".NEXTVAL from dual";
		System.out.println("seqSql is " + seqSql);
		Object pk =
			  doSearch(seqSql).get(0).get("nextval");

		Map metaData = getMetaData(rec);
		List columnNamesNotIncludingId = (List) metaData.get(SqlDao.COLUMN_NAMES);
		List params = new LinkedList();
		params.add(pk);
		for(int i = 0, m = columnNamesNotIncludingId.size(); i < m; i++){
			String columName = (String) columnNamesNotIncludingId.get(i);
			if(columName.equals(rec.gVersionFieldName())){
				params.add(new Integer(1));
			}
			else
				params.add(values.get(columName));
		}
		String sql = (String) metaData.get(SqlDao.INSERT_STATMENT);

		updateDelOrInsert(sql, params);

		values.put(rec.gIdName(), pk);

	}


	private Map createOrFindMetaData(String sql, ResultSet rs, Map<String, Map> cache) throws SQLException{
		Map ourMetaData = (cache != null) ? cache.get(sql) : null;
		if(ourMetaData == null){
			ourMetaData = new HashMap();
			ResultSetMetaData rsMetaData = rs.getMetaData();
			int count = rsMetaData.getColumnCount();
			ourMetaData.put(COLUMN_COUNT, count);
			List names = new ArrayList();
			for(int i = 1; i <= count; i++){
				names.add(rsMetaData.getColumnName(i).toLowerCase());
			}
			ourMetaData.put(COLUMN_NAMES, names);
			if(cache != null)
				cache.put(sql, ourMetaData);
		}
		return ourMetaData;
	}

	private int setParameters(PreparedStatement stmt, List params) throws SQLException{
		int i = 1;
		if(params == null)
			return i;
		for(int n = params.size(); i <= n; i++){
			Object obj = params.get(i - 1);
			//For oracle bug.
			if(obj instanceof java.util.Date && handlingTimeDates){
				java.util.Date date = (java.util.Date) obj;
				stmt.setTimestamp(i, new Timestamp(date.getTime()));
			}
			else
				stmt.setObject(i, obj);
		}
		return i;
	}

	protected Connection getConnection(){
		return ConnectionThreadLocal.getConnection();
	}


	public Map getTableCache() {
		return this.tableCache;
	}

	public static void close(ResultSet rs, Statement stmt) {
		try{
			if(rs != null)
				rs.close();
		}catch(SQLException e){
			if(! e.getMessage().startsWith("Result set already closed")){
                e.printStackTrace();
			}
		}
		close(stmt);

	}

	public static void close(Statement stmt) {
		try {
			if (stmt != null)
				stmt.close();
		} catch (SQLException e) {
			//TODO Logging
			System.out.println("Couldn't close statement.");
		}
	}

	public void setHandlingTimeDates(boolean handlingTimeDates) {
		this.handlingTimeDates = handlingTimeDates;
	}


}
